package com.design.working.dao.impl;

import com.design.working.dao.CounselorDAO;
import com.design.working.dto.counselor.CounselorHandleStudentApplicationDTO;
import com.design.working.dto.counselor.SearchCounselorStudentAttendanceDTO;
import com.design.working.po.StudentApplicationDetailPO;
import com.design.working.po.StudentApplicationExaminationPO;
import com.design.working.po.StudentMsgPO;
import com.design.working.po.counselor.CounselorSelectStudentApplicationPO;
import com.design.working.po.counselor.CounselorStudentAttendanceRecordPO;
import com.design.working.util.JDBCUtil;
import com.design.working.vo.counselor.CounselorCollegeStudentInfoVO;
import com.design.working.vo.counselor.CounselorHandleStudentWorkApplicationVO;
import com.design.working.vo.student.JobSelectionVO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.design.working.enums.OperationEnum.NO_INDEX;
import static com.design.working.enums.OperationEnum.NO_PAGE;

public class CounselorDAOImpl implements CounselorDAO {

    /**
     * 查找本院未审批的学生岗位申请记录
     *
     * @param uid  辅导员工号
     * @param page  分页当前页码
     * @param limit 分页每页展示数据
     * @return 查询到的本院未审批的学生岗位申请记录
     */
    @Override
    public List<CounselorHandleStudentWorkApplicationVO> selectStudentWorkApplication(Integer uid, Integer page, Integer limit) {

        List<CounselorHandleStudentWorkApplicationVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT swa.id, m.name, c2.class_name, s.uid, s.username, " +
                    "d.name, j.name, j.detail, swa.student_msg_template_id " +
                    "FROM student_work_application swa " +
                    "JOIN student_msg_template smt ON swa.student_msg_template_id = smt.id " +
                    "JOIN student s ON smt.student_uid = s.uid " +
                    "JOIN classes c2 ON smt.class_id = c2.id " +
                    "JOIN major m ON c2.major_id = m.id " +
                    "JOIN job j ON swa.job_id = j.id " +
                    "JOIN dept d ON j.dept_id = d.id " +
                    "JOIN college c ON m.college_id = c.id " +
                    "JOIN counselor_msg cm ON cm.college_id = c.id " +
                    "WHERE swa.is_counselor_passed = 0 AND swa.is_deleted = 0 AND cm.uid = ?";

            //分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            //查找本院未被辅导员处理的申请
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                //专业班级
                String majorClass = resultSet.getString(2) + "-" + resultSet.getString(3);
                list.add(new CounselorHandleStudentWorkApplicationVO(
                        resultSet.getInt(1),
                        majorClass,
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 插入辅导员对学生申请记录的审批内容
     *
     * @param counselorId 辅导员id
     * @param counselorHandleStudentApplicationDTO 辅导员对学生申请记录的审批内容
     */
    @Override
    public void insertStudentApplicationHanding(Integer counselorId, CounselorHandleStudentApplicationDTO counselorHandleStudentApplicationDTO) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE student_work_application " +
                    "SET counselor_id = ?,counselor_text = ?,is_counselor_passed = ? " +
                    "WHERE id = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, counselorId);
            preparedStatement.setString(2, counselorHandleStudentApplicationDTO.getAdvice());
            preparedStatement.setInt(3, counselorHandleStudentApplicationDTO.getOperation());
            preparedStatement.setInt(4, counselorHandleStudentApplicationDTO.getId());
            //变更该条学生岗位申请记录的信息
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(preparedStatement, connection);
        }
    }

    /**
     * 通过工号查找辅导员在数据库中id
     *
     * @param uid 辅导员工号
     * @return 辅导员在数据库中id
     */
    @Override
    public int selectCounselorId(Integer uid) {

        int id = NO_INDEX.getVal();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id " +
                    "FROM counselor " +
                    "WHERE uid = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                id = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return id;
    }

    /**
     * 查找申请表中学生申请工作填写的个人信息
     *
     * @param smtId 学生申请工作表中对应的个人信息记录id
     * @return 学生申请工作填写的个人信息
     */
    @Override
    public StudentApplicationDetailPO selectStudentApplicationDetail(Integer smtId) {

        StudentApplicationDetailPO studentApplicationDetailPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT phone, email, political_status, needy_student, dorm, strength, free_time " +
                    "FROM student_msg_template " +
                    "WHERE id = ? " +
                    "AND is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, smtId);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationDetailPO = new StudentApplicationDetailPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationDetailPO;
    }

    /**
     * （申请岗位）查找全部岗位
     *
     * @param place 需要查询的岗位所属的行政分类（job_category）
     * @param page  分页当前页码
     * @param limit 分页每页展示数据
     * @return 查询到的岗位
     */
    @Override
    public List<JobSelectionVO> selectJob(String place, Integer page, Integer limit) {

        List<JobSelectionVO> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            // 四表联结
            String sql = "SELECT DISTINCT job.id, dept.name, job.name, dept_employee.username, job.place, job.salary, job_remain.remain, job.detail " +
                    "FROM dept, job, dept_employee, job_remain, job_category " +
                    "WHERE job.dept_id = dept.id AND job.dept_employee_id = dept_employee.id " +
                    "AND job.job_category_id = (SELECT job_category.id FROM job_category WHERE job_category.name = ?) " +
                    "AND job.id = job_remain.job_id AND job.is_deleted = 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, place);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new JobSelectionVO(resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getBigDecimal(6),
                        resultSet.getInt(7),
                        resultSet.getString(8)));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查找本院学生信息
     *
     * @param uid   辅导员工号
     * @param page  分页当前页码
     * @param limit 分页每页展示数据
     * @return 查询到的本院学生信息
     */
    @Override
    public List<CounselorCollegeStudentInfoVO> selectStudentInfo(Integer uid, Integer page, Integer limit) {

        List<CounselorCollegeStudentInfoVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT major.name, classes.class_name, student.uid, student.username " +
                    "FROM student " +
                    "JOIN student_msg sm ON student.uid = sm.uid " +
                    "JOIN classes ON sm.class_id = classes.id " +
                    "JOIN major ON classes.major_id = major.id " +
                    "JOIN college ON major.college_id = college.id " +
                    "JOIN counselor_msg ON counselor_msg.college_id = college.id " +
                    "WHERE counselor_msg.uid = ? AND sm.is_deleted = 0 ";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String majorClass = resultSet.getString(1) + "-" + resultSet.getString(2);
                list.add(new CounselorCollegeStudentInfoVO(
                        majorClass,
                        resultSet.getInt(3),
                        resultSet.getString(4)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名查找本院学生信息
     *
     * @param uid         辅导员工号
     * @param page        分页当前页码
     * @param limit       分页每页展示数据
     * @param stuUid      学生学号
     * @param studentName 学生姓名
     * @return 查询到的本院学生信息
     */
    @Override
    public List<CounselorCollegeStudentInfoVO> selectStudentInfoByName(Integer uid, Integer page, Integer limit, String stuUid, String studentName) {

        List<CounselorCollegeStudentInfoVO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT major.name, classes.class_name, student.uid, student.username " +
                    "FROM student " +
                    "JOIN student_msg sm ON student.uid = sm.uid " +
                    "JOIN classes ON sm.class_id = classes.id " +
                    "JOIN major ON classes.major_id = major.id " +
                    "JOIN college ON major.college_id = college.id " +
                    "JOIN counselor_msg ON counselor_msg.college_id = college.id " +
                    "WHERE counselor_msg.uid = ? AND sm.is_deleted = 0 " +
                    "AND INSTR(student.uid, ?) > 0 AND INSTR(student.username, ?) > 0";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            preparedStatement.setString(2, stuUid);
            preparedStatement.setString(3, studentName);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(4, page);
                preparedStatement.setInt(5, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String majorClass = resultSet.getString(1) + "-" + resultSet.getString(2);
                list.add(new CounselorCollegeStudentInfoVO(
                        majorClass,
                        resultSet.getInt(3),
                        resultSet.getString(4)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询学生详细信息
     *
     * @param uid 学生学号
     * @return 学生个人详细信息
     */
    @Override
    public StudentMsgPO selectStudentMsg(Integer uid) {

        StudentMsgPO studentMsgPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT gender, phone, email, political_status, is_needy_student, dorm, strength " +
                    "FROM student_msg " +
                    "WHERE uid = ?";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentMsgPO = new StudentMsgPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getInt(5),
                        resultSet.getString(6),
                        resultSet.getString(7)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentMsgPO;
    }

    /**
     * 查询本院学生考勤记录
     *
     * @param uid         辅导员工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @return 学生考勤记录
     */
    @Override
    public  List<CounselorStudentAttendanceRecordPO> selectCollegeStudentAttendance(Integer uid, Integer page, Integer limit) {

        List<CounselorStudentAttendanceRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT student.uid, student.username, ct.year, ct.term, d.name, j.name, " +
                    "de.username, swa.working_date, swa.is_attend " +
                    "FROM student_work_attendance swa " +
                    "JOIN student_to_work stw on swa.student_to_work_id = stw.id " +
                    "JOIN dept_employee de on de.id = swa.dept_employee_id " +
                    "JOIN student on stw.student_uid = student.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on ct.id = j.campus_term_id " +
                    "JOIN student_msg sm ON student.uid = sm.uid " +
                    "JOIN classes ON sm.class_id = classes.id " +
                    "JOIN major ON classes.major_id = major.id " +
                    "JOIN college ON major.college_id = college.id " +
                    "JOIN counselor_msg ON counselor_msg.college_id = college.id " +
                    "WHERE swa.is_deleted = 0 AND counselor_msg.uid = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new CounselorStudentAttendanceRecordPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名查询本院学生考勤记录
     *
     * @param uid       辅导员工号
     * @param page      分页页数
     * @param limit     每页数据条数
     * @param searchDTO 搜索的信息
     * @return 查询到的通过学号姓名查询本院学生考勤记录
     */
    @Override
    public List<CounselorStudentAttendanceRecordPO> selectCollegeStudentAttendanceByName(Integer uid, Integer page, Integer limit, SearchCounselorStudentAttendanceDTO searchDTO) {

        List<CounselorStudentAttendanceRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT student.uid, student.username, ct.year, ct.term, d.name, j.name, " +
                    "de.username, swa.working_date, swa.is_attend " +
                    "FROM student_work_attendance swa " +
                    "JOIN student_to_work stw on swa.student_to_work_id = stw.id " +
                    "JOIN dept_employee de on de.id = swa.dept_employee_id " +
                    "JOIN student on stw.student_uid = student.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on ct.id = j.campus_term_id " +
                    "JOIN student_msg sm ON student.uid = sm.uid " +
                    "JOIN classes ON sm.class_id = classes.id " +
                    "JOIN major ON classes.major_id = major.id " +
                    "JOIN college ON major.college_id = college.id " +
                    "JOIN counselor_msg ON counselor_msg.college_id = college.id " +
                    "WHERE INSTR(student.uid, ?) > 0 AND INSTR(student.username, ?) > 0 " +
                    "AND swa.is_deleted = 0 AND counselor_msg.uid = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, searchDTO.getId());
            preparedStatement.setString(2, searchDTO.getName());
            preparedStatement.setInt(3, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(4, page);
                preparedStatement.setInt(5, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new CounselorStudentAttendanceRecordPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名和时间范围查询本院学生考勤记录
     *
     * @param uid       辅导员工号
     * @param page      分页页数
     * @param limit     每页数据条数
     * @param searchDTO 搜索的信息
     * @return 查询到的通过学号姓名和时间范围查询本院学生考勤记录
     */
    @Override
    public List<CounselorStudentAttendanceRecordPO> selectCollegeStudentAttendanceByTime(Integer uid, Integer page, Integer limit, SearchCounselorStudentAttendanceDTO searchDTO) {

        List<CounselorStudentAttendanceRecordPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT student.uid, student.username, ct.year, ct.term, d.name, j.name, " +
                    "de.username, swa.working_date, swa.is_attend " +
                    "FROM student_work_attendance swa " +
                    "JOIN student_to_work stw on swa.student_to_work_id = stw.id " +
                    "JOIN dept_employee de on de.id = swa.dept_employee_id " +
                    "JOIN student on stw.student_uid = student.uid " +
                    "JOIN job j on stw.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on ct.id = j.campus_term_id " +
                    "JOIN student_msg sm ON student.uid = sm.uid " +
                    "JOIN classes ON sm.class_id = classes.id " +
                    "JOIN major ON classes.major_id = major.id " +
                    "JOIN college ON major.college_id = college.id " +
                    "JOIN counselor_msg ON counselor_msg.college_id = college.id " +
                    "WHERE INSTR(student.uid, ?) > 0 AND INSTR(student.username, ?) > 0 " +
                    "AND swa.working_date between ? and ? AND swa.is_deleted = 0 AND counselor_msg.uid = ?";


            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, searchDTO.getId());
            preparedStatement.setString(2, searchDTO.getName());
            preparedStatement.setString(3, searchDTO.getBeginDate());
            preparedStatement.setString(4, searchDTO.getEndDate());
            preparedStatement.setInt(5, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(6, page);
                preparedStatement.setInt(7, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new CounselorStudentAttendanceRecordPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询本院学生申请岗位的信息
     * @param uid         辅导员工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @return 学生申请岗位的信息
     */
    @Override
    public List<CounselorSelectStudentApplicationPO> selectStudentApplication(Integer uid, Integer page, Integer limit) {

        List<CounselorSelectStudentApplicationPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, d.name, j.name, " +
                    "swa.is_counselor_passed, j.detail, swa.id, smt.id " +
                    "FROM student_work_application swa " +
                    "JOIN student_msg_template smt on swa.student_msg_template_id = smt.id " +
                    "JOIN student s on s.uid = smt.student_uid " +
                    "JOIN job j on swa.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "JOIN student_msg sm ON s.uid = sm.uid " +
                    "JOIN classes ON sm.class_id = classes.id " +
                    "JOIN major ON classes.major_id = major.id " +
                    "JOIN college ON major.college_id = college.id " +
                    "JOIN counselor_msg ON counselor_msg.college_id = college.id " +
                    "WHERE swa.is_deleted = 0 AND counselor_msg.uid = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(2, page);
                preparedStatement.setInt(3, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new CounselorSelectStudentApplicationPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getInt(9),
                        resultSet.getInt(10)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 通过学号姓名查询本院学生申请岗位的信息
     * @param uid         辅导员工号
     * @param page        分页页数
     * @param limit       每页数据条数
     * @param stuUid      学生学号
     * @param studentName 学生姓名
     * @return 学生申请岗位的信息
     */
    @Override
    public List<CounselorSelectStudentApplicationPO> selectStudentApplicationByName(Integer uid, Integer page, Integer limit, String stuUid, String studentName) {

        List<CounselorSelectStudentApplicationPO> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT s.uid, s.username, ct.year, ct.term, d.name, j.name, " +
                    "swa.is_counselor_passed, j.detail, swa.id, smt.id " +
                    "FROM student_work_application swa " +
                    "JOIN student_msg_template smt on swa.student_msg_template_id = smt.id " +
                    "JOIN student s on s.uid = smt.student_uid " +
                    "JOIN job j on swa.job_id = j.id " +
                    "JOIN dept d on j.dept_id = d.id " +
                    "JOIN campus_term ct on j.campus_term_id = ct.id " +
                    "JOIN student_msg sm ON s.uid = sm.uid " +
                    "JOIN classes ON sm.class_id = classes.id " +
                    "JOIN major ON classes.major_id = major.id " +
                    "JOIN college ON major.college_id = college.id " +
                    "JOIN counselor_msg ON counselor_msg.college_id = college.id " +
                    "WHERE INSTR(s.uid, ?) > 0 AND INSTR(s.username, ?) > 0 " +
                    "AND swa.is_deleted = 0 AND counselor_msg.uid = ?";

            // 分页sql
            if (!page.equals(NO_PAGE.getVal())) {
                sql += " limit ?,?";
            }

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, stuUid);
            preparedStatement.setString(2, studentName);
            preparedStatement.setInt(3, uid);

            if (!page.equals(NO_PAGE.getVal())) {
                preparedStatement.setInt(4, page);
                preparedStatement.setInt(5, limit);
            }

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                list.add(new CounselorSelectStudentApplicationPO(
                        resultSet.getInt(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getInt(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getInt(7),
                        resultSet.getString(8),
                        resultSet.getInt(9),
                        resultSet.getInt(10)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /**
     * 查询学生岗位申请的审批详情
     * @param swaId 学生岗位申请表id
     * @return 查询到的学生岗位申请审批详情
     */
    @Override
    public StudentApplicationExaminationPO selectStuAppExamination(Integer swaId) {

        StudentApplicationExaminationPO studentApplicationExaminationPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed, de.username, " +
                    "dept_text, is_dept_passed, a.username, admin_text, is_admin_passed " +
                    "FROM student_work_application swa " +
                    "JOIN counselor c on swa.counselor_id = c.id " +
                    "JOIN admin a on swa.admin_id = a.id " +
                    "JOIN dept_employee de on swa.dept_employee_id = de.id " +
                    "WHERE swa.id = ? AND swa.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationExaminationPO = new StudentApplicationExaminationPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationExaminationPO;
    }

    /**
     * 查询部门员工和管理员都未审批的学生岗位申请审批详情
     * @param swaId 学生岗位申请表id
     * @return 查询到的部门员工和管理员都未审批的学生岗位申请审批详情
     */
    @Override
    public StudentApplicationExaminationPO selectStuAppExamination0(Integer swaId) {

        StudentApplicationExaminationPO studentApplicationExaminationPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed " +
                    "FROM student_work_application swa " +
                    "JOIN counselor c on swa.counselor_id = c.id " +
                    "WHERE swa.id = ? AND swa.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationExaminationPO = new StudentApplicationExaminationPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        "",
                        "",
                        0,
                        "",
                        "",
                        0
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationExaminationPO;
    }

    /**
     * 查询管理员未审批的学生岗位申请审批详情
     * @param swaId 学生岗位申请表id
     * @return 查询到的管理员未审批的学生岗位申请审批详情
     */
    @Override
    public StudentApplicationExaminationPO selectStuAppExamination1(Integer swaId) {

        StudentApplicationExaminationPO studentApplicationExaminationPO = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT c.username, counselor_text, is_counselor_passed, " +
                    "de.username, dept_text, is_dept_passed " +
                    "FROM student_work_application swa " +
                    "JOIN counselor c on swa.counselor_id = c.id " +
                    "JOIN dept_employee de on swa.dept_employee_id = de.id " +
                    "WHERE swa.id = ? AND swa.is_deleted = 0";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, swaId);

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                studentApplicationExaminationPO = new StudentApplicationExaminationPO(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getInt(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getInt(6),
                        "",
                        "",
                        0
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return studentApplicationExaminationPO;
    }

    /**
     * 查询当前学生岗位申请是否已被辅导员和部门员工以及管理员审批
     *
     * @param swaId 学生岗位申请表id
     * @return 0-三者都未审批; 1-辅导员已审批；2-部门员工已审批；3-三者都已审批
     */
    @Override
    public int isHandleStuApp(Integer swaId) {

        int handleType = 0;//默认都未审批

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtil.getConnection();

            String sql1 = "SELECT id FROM student_work_application WHERE id = ? AND is_counselor_passed <> 0";
            String sql2 = "SELECT id FROM student_work_application WHERE id = ? AND is_dept_passed <> 0";
            String sql3 = "SELECT id FROM student_work_application WHERE id = ? AND is_admin_passed <> 0";

            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                handleType = 3;
                return handleType;
            }

            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                handleType = 2;
                return handleType;
            }

            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, swaId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                handleType = 1;
                return handleType;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(resultSet, preparedStatement, connection);
        }
        return handleType;
    }
}
